 |
| |
|
Oracle > Setting up new Schemas and Tables articles: • An overview of setting up a new schema and table in Oracle 9i
|
Return to index of articles
An overview of setting up a new schema and table in Oracle 9i
Category: Oracle
Category: Setting up new Schemas and Tables
Setting up a new Schema in Oracle
Summary:
1. create the TableSpace
2. create the user
3. set the user's Quota for the TableSpace
4. create the Schema
5. create tables etc. as necessary
Create a TableSpace for the Schema under Storage > TableSpaces
In Security > Users: Create a new user with the desired Schema name as the User name
On the Quota tab make sure you set this user's quota for the desired TableSpace to Unlimited or the desired value in KB. Click on the Tablespace to highlight it then change the value at the bottom of the screen and click Apply
To create the Schema in SQL Plus:
CREATE SCHEMA AUTHORIZATION
is the name of both the logged in user and the Schema which will be created.
Once the Schema has been created, tables etc. can be added from Oracle Enterprise Manager.
Creating a Trigger to update an Autonumber (Self-incrementing) field
1. Create a Sequence with the desired name (use a name similar to the related table or field)
Schema > schemaname > Sequences
The default values will create a Sequence which starts at 1 and increments by 1 each time.
Schema > schemaname > Tables > tablename > Triggers: Create a new trigger for the desired Table
In Trigger body enter the following, replacing sequencename with the name of the Sequence created above and fieldname with the name of the field which should be automatically filled in:
DECLARE m_no NUMBER;
BEGIN
SELECT sequencename.nextval INTO :new.fieldname FROM dual;
END;
On the Trigger Event tab choose Before and Insert
On the Advanced tab choose Trigger for each row
Creating a trigger to automatically insert today's date into a "MODIFIED_DATE" field to record modifications to a record
Set the trigger to fire before the insert or update of any desired columns, and choose Trigger for each row
Trigger body:
begin
:new.EVENT_MOD_DATE := sysdate;
end;
Creating a trigger to store the IP Address of the connected client (will only store the IP address of the web server if the client is using a web browser)
begin
Select SYS_CONTEXT('USERENV','IP_ADDRESS') INTO :new.FIELDNAME FROM Dual;
end;
Connecting to an Oracle database with ASP VBScript and ADO
Assuming a standard install of Oracle 9i on IIS and tnsname is a valid TNS name listed in TNSNAMES.ORA :
<%
' CREATE THE ORACLE DATABASE CONNECTION
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("tnsname", "username/password", Cint(0))
' set up SQL statement
strSQL = "select * from TBL_NAME ORDER BY FIELD_NAME"
' populate recordset from ORACLE connection
Set OraDynaset = OraDatabase.DbCreateDynaset(strSQL, cint(0))
if OraDynaset.EOF then
response.write("No records found.")
else
' loop through records
do until OraDynaset.EOF
' display data here
response.write("Record found") ' display real info here
response.write( OraDynaset("fieldname") )
OraDynaset.moveNext
loop
end if
%>
To connect to Oracle using standard ADO / ODBC objects
In Windows 2000 Server: Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC)
Add a System DSN to connect to the Oracle database: click on the System DSN tab, click Add, choose Oracle in OraHome (or similar) as the driver. Again this assumes that tnsname is a valid TNS name which appears in the TNSNAMES.ORA
9/1/2003
|
|
|
|
 |
|
| |
All contents ©2003 Ten Volt Consulting.
All rights reserved.
Unauthorized duplication or use is a violation of applicable laws.
Webmaster Contact |
